﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using com.nm.ApiServer;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using DistributeAPI.Model;
using NHibernateVertica;
using george.Data;
using george.Helper;
using Vertica.Data.VerticaClient;

namespace DistributeAPI
{
    /// <summary>
    /// 计算企划订单量API
    /// </summary>
    public class PlanningOrderQuantityHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region Insert 新增

        [MustLogin]
        [JsonType]
        [Description("新增")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Insert([ParamDescription("要插入的数据")] PlanningOrderQuantityModelSizeList param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.MaterialID))
            {
                return Error($"新增失败！货号不能为空");
            }
            param.MaterialID = param.MaterialID.Trim();
            if (string.IsNullOrWhiteSpace(param.ShopCode))
            {
                return Error($"新增失败！店铺编号不能为空");
            }
            if (string.IsNullOrWhiteSpace(param.TotalQty.ToString()))
            {
                return Error($"新增失败！SKC总量不能为空");
            }
            if (param.TotalQty <= 0 || param.TotalQty > 999)
            {
                return Error($"新增失败！SKC总量范围在1 - 999");
            }
            if (param.SizeList != null && param.SizeList.Count < 1)
            {
                return Error($"新增失败！最低深度不能为空");
            }
            #endregion

            //获取最低深度
            //param.SizeList = GetMinQtySizeList(param.MaterialID, param.ShopCode);

            #region 检测该是否已存在，避免插入重复数据

            foreach (var item in param.SizeList)
            {
                if (AlreadyExists(param.ShopCode, param.MaterialID, item.SizeLength, item.SizeID))
                {
                    return Error($"新增失败！\r\n{param.ShopName},货号{param.MaterialID},尺码名称{item.SizeName}已存在");
                }
            }

            #endregion

            //计算
            Compute(param);

            #region 插入新数据
            var list = new List<CommandInfo>();
            foreach (var item in param.SizeList)
            {
                string insertSql = $@"
                INSERT
	                INTO
		                ngoods.ngoods_DC_Bas_PLOrder(
			                ExtrSource,
			                CompanyID,
			                ShopID,
			                ShopCode,
			                MaterialID,
			                SizeID,
			                Qty,
			                TotalQty,
			                SizeLength,
			                SizeMinQty,
			                MofifyDTM
		                )
	                VALUES(
		                '{UserInfo.UserExtrSource}',
		                '{UserInfo.UserCompanyId}',
		                '{param.ShopID}',
		                '{param.ShopCode}',
		                '{param.MaterialID}',
		                '{item.SizeID}',
		                '{item.SizeQty}',
		                '{param.TotalQty}',
		                '{item.SizeLength}',
		                '{item.MinQty}',
		                '{param.MofifyDTM}'
	                )
                ";
                list.Add(new CommandInfo(insertSql, null));
            }

            #endregion

            int n = Helper.ExecuteSqlTran(list);
            if (n > 0)
            {
                return Success(0);
            }
            return Error("插入失败,请重试");
        }
        public bool AlreadyExists(string shopCode, string materialID, string sizeLength, string sizeID)
        {
            bool result = false;
            try
            {
                string strSql = $@"
                            SELECT COUNT(CompanyID) as COUNT
                            FROM ngoods.ngoods_DC_Bas_PLOrder
                            WHERE shopCode ='{shopCode}' and MaterialID='{materialID}' 
                                           and  ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'
            ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                if (count > 0)
                {
                    result = true;
                }
            }
            catch (Exception e)
            {
                throw;
            }

            return result;
        }


        #endregion

        #region Update 更新

        [MustLogin]
        [JsonType]
        [Description("更新")]
        [ReturnType(typeof(int), "0－成功")]
        public ApiResult Update([ParamDescription("更新参数")] PlanningOrderQuantityModelSizeList param)
        {
            #region 前端参数验证

            if (string.IsNullOrWhiteSpace(param.ShopCode) || string.IsNullOrWhiteSpace(param.OldShopCode))
            {
                return Error($"更新失败！店铺编号不能为空");
            }

            if (string.IsNullOrWhiteSpace(param.MaterialID) || string.IsNullOrWhiteSpace(param.OldMaterialID))
            {
                return Error($"更新失败！货号不能为空");
            }
            param.MaterialID = param.MaterialID.Trim();

            if (param.TotalQty <= 0 || param.TotalQty > 999)
            {
                return Error($"更新失败！SKC总量范围在1 - 999");
            }

            if (param.SizeList != null && param.SizeList.Count < 1)
            {
                return Error($"更新失败！最低深度不能为空");
            }
            #endregion

            var list = new List<CommandInfo>();
            int n = 0;

            #region 如果是导入已计算的订单

            //如果是导入已计算的订单,只保存店铺信息,skc总量和货号
            //不进行计算
            if (param.Flag == 1)
            {
                //更新前删除新的shopcode和货号对应的数据中的数据
                //删除导入后再计算的相关订单
                string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_DC_Bas_PLOrder
                                    where IFNULL(flag,0) <> 1 and shopcode='{param.OldShopCode}' and MaterialID='{param.OldMaterialID}' 
                                                 ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'                                           
                ";
                //更新数据
                string updateSql = $@"
                update
		                ngoods.ngoods_DC_Bas_PLOrder
                 set 
                        ShopCode = '{param.ShopCode}',
                        ShopID = '{param.ShopID}',
                        {(param.ShopCode == param.OldShopCode ? "" : $"OldShopCode = '{param.OldShopCode}',")}                        
                        MaterialID = '{param.MaterialID}',
                        TotalQty = '{param.TotalQty}',
                        SizeLength = '{param.SizeList.FirstOrDefault().SizeLength}',
                        MofifyDTM = '{param.MofifyDTM}'
	              where
                          shopcode =  '{param.OldShopCode}' and 
                          MaterialID = '{param.OldMaterialID}'  and 
                          ifnull(SizeLength,'0')='{param.SizeList.FirstOrDefault().OldSizeLength}' and
                          ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'                                           
                ";
                list.Add(new CommandInfo(deleteSql, null));
                list.Add(new CommandInfo(updateSql, null));
                n = Helper.ExecuteSqlTran(list);
            }

            #endregion 如果是导入已计算的订单

            #region 如果是未计算的订单

            else
            {
                //获取最低深度
                //param.SizeList = GetMinQtySizeList(param.MaterialID, param.ShopCode);
                //计算
                Compute(param);
                //删除旧数据
                string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_DC_Bas_PLOrder
                                    where shopcode='{param.OldShopCode}' and MaterialID='{param.OldMaterialID}' 
                                    and ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}'                                           
                ";
                list.Add(new CommandInfo(deleteSql, null));
                #region 插入新数据

                foreach (var item in param.SizeList)
                {
                    string insertSql = $@"
                INSERT
	                INTO
		                ngoods.ngoods_DC_Bas_PLOrder(
                            ExtrSource,
			                CompanyID,
			                ShopID,
			                ShopCode,
			                MaterialID,
			                SizeID,
			                Qty,
			                TotalQty,
			                SizeLength,
			                SizeMinQty,
			                MofifyDTM
		                )
	                VALUES(
		                '{UserInfo.UserExtrSource}',
		                '{UserInfo.UserCompanyId}',
		                '{param.ShopID}',
		                '{param.ShopCode}',
		                '{param.MaterialID}',
		                '{item.SizeID}',
		                '{item.SizeQty}',
		                '{param.TotalQty}',
		                '{item.SizeLength}',
		                '{item.MinQty}',
		                '{param.MofifyDTM}'
	                )
                ";
                    list.Add(new CommandInfo(insertSql, null));

                    #endregion
                }
                n = Helper.ExecuteSqlTran(list);
            }
            #endregion 如果是未计算的订单

            if (n > 0)
            {
                return Success(0);
            }
            return Error("更新失败,请重试");
        }

        #endregion

        #region GetSizeList 获取尺码列表

        /// <summary>
        /// 如果本店铺找不到,就取同城的尺码列表
        /// 同城的找不到,就取同省的, 同区域的,全国的
        /// </summary>
        /// <param name="MaterialID"></param>
        /// <param name="ShopID"></param>
        /// <returns></returns>
        [MustLogin]
        [Description("获取尺码列表")]
        [ReturnType(typeof(PaperData<PlanningOrderQuantityModelSize>))]
        public ApiResult GetSizeList(
            [ParamDescription("货号")] string MaterialID = "",
            [ParamDescription("店铺编码")] string ShopCode = "")
        {
            try
            {
                var result = GetMinQtySizeList(MaterialID, ShopCode);

                if (result != null && result.Count > 0)
                {
                    var resultGroup = result.GroupBy(p => p.SizeLength).OrderByDescending(p => p.Max(s => s.SizeLength)).ToList();
                    return Success(resultGroup);
                }
                else
                {
                    return Error($"货号{MaterialID},店铺编码{ShopCode}查不到对应的最低深度");
                }
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        public IList<PlanningOrderQuantityModelSize> GetMinQtySizeList([ParamDescription("货号")] string MaterialID = "",
            [ParamDescription("店铺编码")] string ShopCode = "")
        {
            MaterialID = MaterialID.Trim();

            #region 查找店铺的最低深度
            //查找店铺的最低深度
            var listSql = $@"
SELECT
DISTINCT
	stt.SizeID,
	stt.SizeName,
	m.minqty,
	m.modelid,
    m.SizeTypeFlag,
    m.SizeLength,
    ms.SizeCodeSort
from
	nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
	and s.shopcode = '{ShopCode}'
ORDER by
	m.SizeLength,ms.sizecodesort
                                ";
            var dt = Helper.ExecuteDataTable(new VerticaCommand(listSql, null));
            var list = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dt);
            #endregion 查找店铺的最低深度

            #region 取本市其他店铺的本货号最低深度
            //如果找不到,就取本市其他店铺的本货号最低深度
            if (list == null || list.Count == 0)
            {
                var listSqlCity = $@"
                                select
DISTINCT
	s.shopid,
	s.shopname,
	stt.SizeID,
	stt.SizeName,
	ms.SizeCodeSort,
    m.SizeTypeFlag,    
    m.SizeLength,
	m.modelid
from
	nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
	and s.shopid =(
		SELECT
			s.shopid
		from
				nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
			and s.city_name =(
				select
					city_name
				from
					ndw.ndw_Shop
				where
					shopcode = '{ShopCode}'	and ExtrSource = '{UserInfo.UserExtrSource}'	and CompanyID = '{UserInfo.UserCompanyId}'
			)
			and m.MinQty is not null LIMIT 1
	)
order by
	m.SizeLength,ms.SizeCodeSort

                                ";
                var dtCity = Helper.ExecuteDataTable(new VerticaCommand(listSqlCity, null));
                list = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtCity);
            }
            #endregion 取本市其他店铺的本货号最低深度

            #region 取本省其他店铺的本货号最低深度

            //如果找不到,就取本省其他店铺的本货号最低深度
            if (list == null || list.Count == 0)
            {
                var listSqlCity = $@"
                                select
DISTINCT
	s.shopid,
	s.shopname,
	stt.SizeID,
	stt.SizeName,
	ms.SizeCodeSort,
    m.SizeTypeFlag,
    m.SizeLength,
	m.modelid
from
		nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
	and s.shopid =(
		SELECT
			s.shopid
		from
				nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
			and s.province_name =(
				select
					province_name
				from
					ndw.ndw_Shop
				where
						shopcode = '{ShopCode}'	and ExtrSource = '{UserInfo.UserExtrSource}'	and CompanyID = '{UserInfo.UserCompanyId}'
			)
			and m.MinQty is not null LIMIT 1
	)
order by
	m.SizeLength,ms.sizecodesort
                                ";
                var dtCity = Helper.ExecuteDataTable(new VerticaCommand(listSqlCity, null));
                list = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtCity);
            }
            #endregion 取本省其他店铺的本货号最低深度

            #region 取本店铺所在商品区域其他店铺的本货号最低深度

            //如果找不到,就取本店铺所在商品区域其他店铺的本货号最低深度
            if (list == null || list.Count == 0)
            {
                var listSqlCity = $@"
                                  select
DISTINCT
	s.shopid,
	s.shopname,
	stt.SizeID,
	stt.SizeName,
	ms.SizeCodeSort,
    m.SizeTypeFlag,
    m.SizeLength,
	m.modelid
from
		nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
			 left join ndw.ndw_AreaProCity apc
             on s.AreaID = apc.AreaID and s.ExtrSource = apc.ExtrSource and s.CompanyID = apc.CompanyID
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
	and s.shopid =(
		SELECT
			s.shopid
		from
		    nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
			 left join ndw.ndw_AreaProCity apc
             on s.AreaID = apc.AreaID and s.ExtrSource = apc.ExtrSource and s.CompanyID = apc.CompanyID
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
			and apc.ParentAreaID = (SELECT a.ParentAreaID from ndw.ndw_Shop s
								left join   ndw.ndw_AreaProCity a
								on s.AreaID = a.AreaID and s.ExtrSource = a.ExtrSource and s.CompanyID = a.CompanyID
								WHERE 
                                	s.shopcode = '{ShopCode}'	and s.ExtrSource = '{UserInfo.UserExtrSource}'	and s.CompanyID = '{UserInfo.UserCompanyId}'
                                                            )
			and m.MinQty is not null LIMIT 1
	)
order by
	m.SizeLength,ms.sizecodesort
                                ";
                var dtCity = Helper.ExecuteDataTable(new VerticaCommand(listSqlCity, null));
                list = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtCity);
            }
            #endregion 取本店铺所在商品区域其他店铺的本货号最低深度

            #region 取全国其他店铺的本货号最低深度
            //如果找不到,就取全国其他店铺的本货号最低深度
            if (list == null || list.Count == 0)
            {
                var listSqlCountry = $@"
                                 select
DISTINCT
	s.shopid,
	s.shopname,
	stt.SizeID,
	stt.SizeName,
	ms.SizeCodeSort,
    m.SizeTypeFlag,    
    m.SizeLength,
	m.modelid
from
		nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
	and s.shopid =(
		SELECT
			s.shopid
		from
	nods.nods_erp_SD_Mat_Material2 c 
left join ndw.ndw_SKCSizeType sst on
	c.MaterialID = sst.MaterialID and c.ExtrSource=sst.ExtrSource and c.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode and stt.ExtrSource=sst.ExtrSource and  sst.CompanyID=stt.CompanyID
left join ngoods.ngoods_DC_Bas_ModelMinQty m on
    c.ExtrSource=m.ExtrSource 
	and c.CompanyID = m.CompanyID
	and stt.SizeID = m.SizeID
	and c.KindID = m.KindID
	and c.ModelID = m.ModelID
    and c.ItemID = m.ItemID
	and stt.SizeTypeFlag = m.SizeTypeFlag 
left join ndw.ndw_Shop s on
  m.ExtrSource=s.ExtrSource and m.CompanyID = s.CompanyID  and 
	m.ShopID = s.ShopID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid and 
ms.ExtrSource=stt.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	c.MaterialID = '{MaterialID}'
	and c.ExtrSource = '{UserInfo.UserExtrSource}'	and c.CompanyID = '{UserInfo.UserCompanyId}'
			and m.MinQty is not null 
		order by s.ShopID
		LIMIT 1
	)
order by
	m.SizeLength,ms.sizecodesort
                                ";
                var dtCountry = Helper.ExecuteDataTable(new VerticaCommand(listSqlCountry, null));
                list = ModelConvertHelper<PlanningOrderQuantityModelSize>.ConvertToModel(dtCountry);
            }
            #endregion 取全国其他店铺的本货号最低深度

            return list;
        }

        #endregion

        #region 查询


        #region 获取企划订单量列表

        [MustLogin]
        [Description("获取企划订单量列表")]
        [ReturnType(typeof(IList<PlanningOrderQuantityModel>))]
        public ApiResult GetList(
            [ParamDescription("店铺名称或者店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "",
            [ParamDescription("页码")] int Size = 1,
            [ParamDescription("页数")] int Page = 100)
        {
            var count = ReturnCount(keys, materialId);
            var result = PaperData<IGrouping<string, PlanningOrderQuantityModel>>.Create(count, Size, Page);
            string strSql = $@"
      Select
	o.CompanyID,
	o.ShopID,
	o.ShopCode,
	o.OldShopCode,
	o.MaterialID,
	o.SizeID,
	o.Qty,
	s.ShopName,
	stt.SizeName,
	o.TotalQty,
	o.SizeLength,
	o.SizeMinQty,
    stt.SizeTypeFlag as SizeCodeType,
	o.Flag
FROM
	ngoods.ngoods_DC_Bas_PLOrder o left join ndw.ndw_Shop s on
	o.shopcode = s.shopcode and o.ExtrSource=s.ExtrSource and o.CompanyID=s.CompanyID
left join ndw.ndw_SKCSizeType sst on
	o.MaterialID = sst.MaterialID  and o.ExtrSource=sst.ExtrSource and o.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode  and o.ExtrSource=stt.ExtrSource and o.CompanyID=stt.CompanyID
	and stt.SizeID = o.SizeID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid   and ms.ExtrSource=sst.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	 o.ExtrSource='{UserInfo.UserExtrSource}' and o.CompanyID='{UserInfo.UserCompanyId}'
        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (o.shopcode ilike '%{keys}%' or s.shopname ilike '%{keys}%') ")}
        {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and o.materialId ilike '%{materialId}%' ")}

        ORDER by o.CompanyID,o.ShopID,o.MaterialID,ms.SizeCodeSort
        LIMIT {Size} offset {(Page - 1.0) * Size}	
        ";
            var cmd = Helper.GetSqlStringCommand(strSql);
            var dt = Helper.ExecuteDataTable(cmd);
            var list = ModelConvertHelper<PlanningOrderQuantityModel>.ConvertToModel(dt);
            var groupedList = list.GroupBy(p => p.CompanyID + p.ShopID + p.MaterialID + p.TotalQty + p.SizeLength);
            result.Items = groupedList.ToList();
            return Success(result);
        }

        #endregion 获取企划订单量列表

        #region 获取企划订单量明细

        [MustLogin]
        [Description("获取企划订单量明细")]
        [ReturnType(typeof(IList<PlanningOrderQuantityModel>))]
        public ApiResult GetDetails([ParamDescription("店铺编码")] string shopCode = "",
           [ParamDescription("货号")] string materialID = "",
           [ParamDescription("尺码长度")] string sizeLength = "",
           [ParamDescription("尺码内码")] string sizeID = "")
        {

            try
            {
                string strSql = $@"
        Select
	o.CompanyID,
	o.ShopID,
	o.ShopCode,
	o.OldShopCode,
	o.MaterialID,
	o.SizeID,
	o.Qty,
	s.ShopName,
	stt.SizeName,
	o.TotalQty,
	o.SizeLength,
	o.SizeMinQty,
	o.Flag
FROM
		ngoods.ngoods_DC_Bas_PLOrder o left join ndw.ndw_Shop s on
	o.shopcode = s.shopcode and o.ExtrSource=s.ExtrSource and o.CompanyID=s.CompanyID
left join ndw.ndw_SKCSizeType sst on
	o.MaterialID = sst.MaterialID  and o.ExtrSource=sst.ExtrSource and o.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode  and o.ExtrSource=stt.ExtrSource and o.CompanyID=stt.CompanyID
	and stt.SizeID = o.SizeID 
left join nods.nods_erp_SD_Mat_Size ms on
	stt.SizeID = ms.sizeid   and ms.ExtrSource=sst.ExtrSource and stt.CompanyID='{UserInfo.UserCompanyId}'
where
	 o.ExtrSource='{UserInfo.UserExtrSource}' and o.CompanyID='{UserInfo.UserCompanyId}'
                and o.shopcode='{shopCode}' 
                and o.MaterialID='{materialID}' 
                and o.SizeID='{sizeID}'
                and ifnull(o.SizeLength,'0')='{sizeLength}'
                ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var list = ModelConvertHelper<PlanningOrderQuantityModel>.ConvertToModel(dt);
                return Success(list.FirstOrDefault());
            }
            catch (Exception e)
            {
                throw;
            }
        }

        #endregion 获取企划订单量明细

        #region 返回行数


        public int ReturnCount(
            [ParamDescription("店铺名称或者店铺编码")] string keys = "",
            [ParamDescription("货号")] string materialId = "")
        {
            try
            {
                var countSql = string.Empty;
                #region 查询时获取行数

                countSql = $@"

        select count(1) as count 
    From
	 	ngoods.ngoods_DC_Bas_PLOrder o left join ndw.ndw_Shop s on
	o.shopcode = s.shopcode and o.ExtrSource=s.ExtrSource and o.CompanyID=s.CompanyID
left join ndw.ndw_SKCSizeType sst on
	o.MaterialID = sst.MaterialID  and o.ExtrSource=sst.ExtrSource and o.CompanyID=sst.CompanyID
left join ndw.ndw_SizeTextType stt on
	sst.SizeTypeCode = stt.SizeTypeCode  and o.ExtrSource=stt.ExtrSource and o.CompanyID=stt.CompanyID
	and stt.SizeID = o.SizeID 
where
	 o.ExtrSource='{UserInfo.UserExtrSource}' and o.CompanyID='{UserInfo.UserCompanyId}'
        {(string.IsNullOrWhiteSpace(keys) ? "" : $" and (o.shopcode ilike '%{keys}%' or s.shopname ilike '%{keys}%') ")}
        {(string.IsNullOrWhiteSpace(materialId) ? "" : $" and o.materialId ilike '%{materialId}%' ")}

";
                #endregion 查询时获取行数

                var cmd = Helper.GetSqlStringCommand(countSql);

                var count = Convert.ToInt32(Helper.ExecuteScalar(cmd));
                return count;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }


        #endregion 返回行数

        #endregion 查询

        #region 删除
        [JsonType]
        [MustLogin]
        [Description("获取企划订单量列表")]
        [ReturnType(typeof(IList<PlanningOrderQuantityModel>))]
        public ApiResult Remove([ParamDescription("删除实体List")] PlaningOrderQuantityDeleteModelList param)
        {

            if (param == null || param.List.Count < 1)
            {
                return Error($"删除失败!");
            }
            HashSet<string> DeleteSql = new HashSet<string>();
            foreach (var item in param.List)
            {
                string deleteSql = $@"
                                    Delete
                                    FROM ngoods.ngoods_DC_Bas_PLOrder
                                    where shopcode='{item.ShopCode}' and 
                                                 MaterialID='{item.MaterialID}' and
	                                             ExtrSource='{UserInfo.UserExtrSource}' and CompanyID='{UserInfo.UserCompanyId}';
                ";
                DeleteSql.Add(deleteSql);
            }
            StringBuilder strSql = new StringBuilder();
            foreach (var item in DeleteSql)
            {
                strSql.Append(item);
            }
            if (strSql.Length > 0)
            {
                List<CommandInfo> cmdList = new List<CommandInfo>();
                CommandInfo cmd = new CommandInfo { CommandText = strSql.ToString() };
                cmdList.Add(cmd);
                Helper.ExecuteSqlTran(cmdList);
                return Success(0);
            }
            return Error($"删除失败!");
        }



        #endregion 删除

        #region 计算
        private void Compute(PlanningOrderQuantityModelSizeList param)
        {
            try
            {
                if (param.SizeList == null || param.SizeList.Count == 0)
                {
                    throw new Exception("没有对应的尺码信息");
                }
                //新增和批量导入时,如果skc总量 < 最低深度的合计,则直接把最低深度分给对应的尺码的数量
                if (param.TotalQty < param.SizeList.Sum(p => p.MinQty))
                {
                    foreach (var item in param.SizeList)
                    {
                        item.SizeQty = item.MinQty;
                    }
                    return;
                }

                #region 查询城市码比资料

                #region 查询同城的尺码占比
                string strSql = $@" 
SELECT r.SizeID,r.SizeRate,ms.SizeCodeSort from ngoods.ngoods_DC_Bas_CitySizeRate  r               
left join ndw.ndw_Shop s 
                on r.AreaID = s.AreaID  and r.ExtrSource=s.ExtrSource and r.CompanyID=s.CompanyID
left join nods.nods_erp_SD_Mat_Size ms on
	r.SizeID = ms.sizeid  and r.ExtrSource=s.ExtrSource

                WHERE
                   r.ExtrSource='{UserInfo.UserExtrSource}' and r.CompanyID='{UserInfo.UserCompanyId}' 
                    and  ModelID='{param.SizeList.FirstOrDefault().ModelID}' 
                    and  sizecodetype='{param.SizeList.FirstOrDefault().SizeTypeFlag}' 
                    and s.shopcode='{param.ShopCode}'
                  and SizeRate > 0                      
                      order by ms.SizeCodeSort

                ";
                var cmd = Helper.GetSqlStringCommand(strSql);
                var dt = Helper.ExecuteDataTable(cmd);
                var listSizeRate = ModelConvertHelper<PlanningOrderQuantityModelSizeRate>.ConvertToModel(dt);
                #endregion 查询同城的尺码占比
                if (listSizeRate == null || listSizeRate.Count == 0)
                {
                    throw new Exception("没有对应的城市码比资料");
                }
                //先排除城市码比资料中没有出现在最低深度中的尺码
                IList<PlanningOrderQuantityModelSizeRate> rateList = new List<PlanningOrderQuantityModelSizeRate>();
                foreach (var item in param.SizeList)
                {
                    if (listSizeRate.Any(p => p.SizeID == item.SizeID))
                    {
                        rateList.Add(listSizeRate.First(p => p.SizeID == item.SizeID));
                        item.SizeCodeSort = rateList.First(p => p.SizeID == item.SizeID).SizeCodeSort;
                    }
                }
                if (rateList == null || rateList.Count == 0)
                {
                    throw new Exception("没有对应的城市码比资料");
                }
                #endregion 查询城市码比资料


                //重算城市码比资料
                ReComputeSizeRateOfCity(rateList);
                //SKC数量－尺码最低深度 ＝剩余skc数量

                var remainSkc = param.TotalQty - param.SizeList.Sum(p => p.MinQty);

                foreach (var item in param.SizeList)
                {
                    //计算出单个尺码的skc数量
                    decimal sizeSkc = 0;
                    var planningOrderQuantityModelSizeRate = rateList.FirstOrDefault(p => p.SizeID == item.SizeID);
                    if (planningOrderQuantityModelSizeRate != null)
                        sizeSkc = remainSkc * planningOrderQuantityModelSizeRate.SizeRate;
                    var roundSizeSkc = MathHelper.Rounding(Convert.ToDouble(sizeSkc), 0);
                    item.SizeQty = Convert.ToInt32(roundSizeSkc);
                }
                var result = param.SizeList.Sum(p => p.SizeQty) + param.SizeList.Sum(p => p.MinQty) - param.TotalQty;
                //按sizecodesort排序
                var sortedList = param.SizeList.OrderBy(p => p.SizeCodeSort);

                #region 判断大小
                //判断大小
                //结果汇总大于skc总量
                if (result > 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result > 0)
                        {
                            if (item.SizeQty > 0)
                            {
                                item.SizeQty--;
                                result--;
                            }
                        }
                    }
                }
                //结果汇总大于skc总量
                else if (result < 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result < 0)
                        {
                            if (item.SizeQty > 0)
                            {
                                item.SizeQty++;
                                result++;
                            }
                        }
                    }
                }
                #endregion 判断大小

                #region 校验性计算


                //防止出现不加深度之前算出的skc数量都为0的情况
                //这里再次校验性计算一次,不再判断是否为0
                result = param.SizeList.Sum(p => p.SizeQty) + param.SizeList.Sum(p => p.MinQty) + -param.TotalQty;
                //判断大小
                //结果汇总大于skc总量
                if (result > 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result > 0)
                        {
                            item.SizeQty--;
                            result--;
                        }
                    }
                }
                //结果汇总大于skc总量
                else if (result < 0)
                {
                    foreach (var item in sortedList)
                    {
                        if (result < 0)
                        {
                            item.SizeQty++;
                            result++;
                        }
                    }
                }


                #endregion 校验性计算

                //最终的skc数量 = 计算出的skc数量 + 尺码对应的最低深度
                foreach (var item in param.SizeList)
                {
                    item.SizeQty += item.MinQty;
                }

            }
            catch (Exception e)
            {
                throw;
            }
        }

        /// <summary>
        /// 重算尺码
        /// </summary>
        /// <param name="listSizeRate"></param>
        public void ReComputeSizeRateOfCity(IList<PlanningOrderQuantityModelSizeRate> listSizeRate)
        {
            var sum = listSizeRate.Sum(p => p.SizeRate);
            if (sum == 0)
            {
                return;
            }
            foreach (var item in listSizeRate)
            {
                item.SizeRate = item.SizeRate / sum;
            }
        }
        #endregion 计算
    }
}
